﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace OrmSmackdown.AdoNet
{
    public class Exerciser : IExerciser
    {
        SqlConnection _cn;

        public Exerciser()
        {
            _cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString);
        }

        public void Insert()
        {
            var insertCommand = new SqlCommand();
            insertCommand.Connection = _cn;

            _cn.Open();

            #region Create Roles
            insertCommand.CommandText = @"
insert into roles (RoleName, IsForPortal, IsForContact)
     values (@roleName, @isForPortal, @isForContact);
select SCOPE_IDENTITY();";

            insertCommand.Parameters.AddWithValue("@roleName", Constants.ManagerRole);
            insertCommand.Parameters.AddWithValue("@isForContact", false);
            insertCommand.Parameters.AddWithValue("@isForPortal", true);
            var managerRoleId = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@roleName", Constants.EmployeeRole);
            insertCommand.Parameters.AddWithValue("@isForContact", false);
            insertCommand.Parameters.AddWithValue("@isForPortal", true);
            var employeeRoleId = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@roleName", Constants.ContactRole);
            insertCommand.Parameters.AddWithValue("@isForContact", true);
            insertCommand.Parameters.AddWithValue("@isForPortal", false);
            var contactRoleId = insertCommand.ExecuteScalar();
            #endregion

            #region Create Companies
            insertCommand.CommandText = @"
insert into Companies (CompanyName)
               values (@companyName);
select SCOPE_IDENTITY();";

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@companyName", Constants.Company1);
            var company1Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@companyName", Constants.Company2);
            var company2Id = insertCommand.ExecuteScalar();
            #endregion

            #region Add User
            insertCommand.CommandText = @"
insert into People (FirstName, LastName, Email, CompanyId, RoleId, ManagerPersonId, UserType)
            values (@firstName, @lastName, @email, @companyId, @roleId, @managerPersonId, @userType);
select SCOPE_IDENTITY();";

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.User1FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.User1LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.User1Email);
            insertCommand.Parameters.AddWithValue("@companyId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@roleId", managerRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "US");
            var user1Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.User2FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.User2LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.User2Email);
            insertCommand.Parameters.AddWithValue("@companyId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@roleId", employeeRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "US");
            var user2Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.User3FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.User3LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.User3Email);
            insertCommand.Parameters.AddWithValue("@companyId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@roleId", employeeRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "US");
            var user3Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.User4FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.User4LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.User4Email);
            insertCommand.Parameters.AddWithValue("@companyId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@roleId", managerRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "US");
            var user4Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.User5FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.User5LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.User5Email);
            insertCommand.Parameters.AddWithValue("@companyId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@roleId", employeeRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "US");
            var user5Id = insertCommand.ExecuteScalar();
            #endregion

            #region Add Contacts 
            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.Contact1FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.Contact1LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.Contact1Email);
            insertCommand.Parameters.AddWithValue("@companyId", company1Id);
            insertCommand.Parameters.AddWithValue("@roleId", contactRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "CN");
            var contact1Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.Contact2FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.Contact2LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.Contact2Email);
            insertCommand.Parameters.AddWithValue("@companyId", company1Id);
            insertCommand.Parameters.AddWithValue("@roleId", contactRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "CN");
            var contact2Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.Contact3FirstName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.Contact3FirstName);
            insertCommand.Parameters.AddWithValue("@email", Constants.Contact3Email);
            insertCommand.Parameters.AddWithValue("@companyId", company2Id);
            insertCommand.Parameters.AddWithValue("@roleId", contactRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "CN");
            var contact3Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.Contact4LastName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.Contact4LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.Contact4Email);
            insertCommand.Parameters.AddWithValue("@companyId", company2Id);
            insertCommand.Parameters.AddWithValue("@roleId", contactRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "CN");
            var contact4Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.Contact5LastName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.Contact5LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.Contact5Email);
            insertCommand.Parameters.AddWithValue("@companyId", company2Id);
            insertCommand.Parameters.AddWithValue("@roleId", contactRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "CN");
            var contact5Id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@firstName", Constants.Contact6LastName);
            insertCommand.Parameters.AddWithValue("@lastName", Constants.Contact6LastName);
            insertCommand.Parameters.AddWithValue("@email", Constants.Contact6Email);
            insertCommand.Parameters.AddWithValue("@companyId", company2Id);
            insertCommand.Parameters.AddWithValue("@roleId", contactRoleId);
            insertCommand.Parameters.AddWithValue("@managerPersonId", System.DBNull.Value);
            insertCommand.Parameters.AddWithValue("@userType", "CN");
            var contact6Id = insertCommand.ExecuteScalar();
            #endregion

            #region Create Portals 
            insertCommand.CommandText = @"
insert into portals(PortalName, AdminUserId)
            values (@portalName, @adminUserId);
select SCOPE_IDENTITY()";

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@portalName", Constants.Portal1);
            insertCommand.Parameters.AddWithValue("@adminUserId", user1Id);
            var portal1id = insertCommand.ExecuteScalar();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@portalName", Constants.Portal2);
            insertCommand.Parameters.AddWithValue("@adminUserId", user4Id);
            var portal2id = insertCommand.ExecuteScalar();
            #endregion

            #region Add Addresses for Users
            AddAddress(insertCommand, user1Id, "1000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, user2Id, "2000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, user3Id, "3000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, user4Id, "4000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, user5Id, "5000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, user1Id, "1100 Some St.", string.Empty, Constants.TownWeCareAbout, "ST", "12345", "1234", true);
            AddAddress(insertCommand, user2Id, "1200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, user3Id, "1300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, user4Id, "1400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, user5Id, "1500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);

            AddAddress(insertCommand, contact1Id, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, contact2Id, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, contact3Id, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, contact4Id, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, contact5Id, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, contact6Id, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
            AddAddress(insertCommand, contact1Id, "8000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, contact2Id, "8100 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, contact3Id, "8200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, contact4Id, "8300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, contact5Id, "8400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            AddAddress(insertCommand, contact6Id, "8500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            #endregion

            #region Add Users to Portals
            insertCommand.CommandText = @"
insert into PortalPeopleXref(PortalId,PersonId)
                     values(@portalId, @personId)";

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user1Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal1id);
            insertCommand.ExecuteNonQuery();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user2Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal1id);
            insertCommand.ExecuteNonQuery();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user2Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal2id);
            insertCommand.ExecuteNonQuery();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user3Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal1id);
            insertCommand.ExecuteNonQuery();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user3Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal2id);
            insertCommand.ExecuteNonQuery();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user4Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal2id);
            insertCommand.ExecuteNonQuery();

            insertCommand.Parameters.Clear();
            insertCommand.Parameters.AddWithValue("@personId", user5Id);
            insertCommand.Parameters.AddWithValue("@portalId", portal2id);
            insertCommand.ExecuteNonQuery();
            #endregion

            _cn.Close();
        }

        #region AddAddress
        private void AddAddress(SqlCommand cmd, object p, string line1, string line2, string city, string state, string zip, string zip4, bool isDefault)
        {
            cmd.Parameters.Clear();
            cmd.CommandText = @"
insert into Addresses(PersonId, IsDefault, Line1, Line2, City, State, ZipCode, ZipCodePlusFour)
               values(@personId, @isDefault, @line1, @line2, @city, @state, @zipCode, @zipCodePlusFour)";

            cmd.Parameters.AddWithValue("@personId", p);
            cmd.Parameters.AddWithValue("@isDefault", isDefault);
            cmd.Parameters.AddWithValue("@line1", line1);
            cmd.Parameters.AddWithValue("@line2", line2);
            cmd.Parameters.AddWithValue("@city", city);
            cmd.Parameters.AddWithValue("@state", state);
            cmd.Parameters.AddWithValue("@zipCode", zip);
            cmd.Parameters.AddWithValue("@zipCodePlusFour", zip4);
            cmd.ExecuteNonQuery();
        }
        #endregion

        public IList<string> GetUserEmails()
        {
            var emails = new List<String>();
            using (var cmd = new SqlCommand("select email from people where UserType = 'US'", _cn))
            {
                cmd.Connection.Open();

                using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (rdr.Read())
                        emails.Add(rdr["email"].ToString());
                }
            }
            return emails;
        }

        public IList<string> GetContactEmails()
        {
            var emails = new List<String>();
            using(var cmd = new SqlCommand("select email from people where UserType = 'CN'", _cn))
            {
                cmd.Connection.Open();
                
                using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (rdr.Read())
                        emails.Add(rdr["email"].ToString());
                }
            }
            return emails;
        }

        public IList<string> GetPortalAdminEmails()
        {
            var emails = new List<string>();
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"
select pe.email 
  from people pe
  join portals p on p.AdminUserId = pe.PersonId";

                cmd.Connection.Open();
                
                using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (rdr.Read())
                        emails.Add(rdr["email"].ToString());
                }
            }

            return emails;
        }

        public IList<string> GetContactEmailsForCompany(string companyName)
        {
            var emails = new List<string>();
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"
select pe.email 
  from companies c
  join people pe on pe.CompanyId = c.CompanyId
  where c.CompanyName = @companyName";

                cmd.Parameters.AddWithValue("@companyName", companyName);
                cmd.Connection.Open();

                using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (rdr.Read())
                        emails.Add(rdr["email"].ToString());
                }
            }

            return emails;
        }

        public int GetNumberOfPortals()
        {
            int numberPortals = 0;

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"select count(PortalId) from portals";
                cmd.Connection.Open();
                numberPortals = (int)cmd.ExecuteScalar();
                cmd.Connection.Close();
            }

            return numberPortals;
        }

        public int GetNumberOfUsers()
        {
            int numberUsers = 0;

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"select count(PersonId) from People where UserType = 'US'";
                cmd.Connection.Open();
                numberUsers = (int)cmd.ExecuteScalar();
                cmd.Connection.Close();
            }

            return numberUsers;
        }

        public int GetNumberOfContacts()
        {
            int numberContacts = 0;

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"select count(PersonId) from People where UserType = 'CN'";
                cmd.Connection.Open();
                numberContacts = (int)cmd.ExecuteScalar();
                cmd.Connection.Close();
            }

            return numberContacts;
        }

        public string GetDefaultCity(string email)
        {
            string defaultCityName = string.Empty;

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"
select ad.city
  from Addresses ad
  join People p on p.PersonId = ad.PersonId and IsDefault = 1 and p.Email = @email";

                cmd.Parameters.AddWithValue("@email", email);
                cmd.Connection.Open();
                defaultCityName = (string)cmd.ExecuteScalar();
                cmd.Connection.Close();
            }

            return defaultCityName;
        }

        public string GetAdminForPortal(string portalName)
        {
            string defaultCityName = string.Empty;

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"
select pe.Email
  from People pe
  join Portals pr on pe.PersonId = pr.AdminUserId and pr.PortalName = @portalName";

                cmd.Parameters.AddWithValue("@portalName", portalName);
                cmd.Connection.Open();
                defaultCityName = (string)cmd.ExecuteScalar();
                cmd.Connection.Close();
            }

            return defaultCityName;
        }

        public double GetAverageUsersPerPortal()
        {
            double averageUserPerPortals = 0.0;

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = _cn;
                cmd.CommandText = @"
select AVG(ppu.userCount)
from (
select convert(float,COUNT(PersonId)) userCount
  from PortalPeopleXref
  group by PortalId) ppu";

                cmd.Connection.Open();
                averageUserPerPortals = (double)cmd.ExecuteScalar();
                cmd.Connection.Close();
            }

            return averageUserPerPortals;
        }

        public void Dispose()
        {
            _cn.Dispose();
        }
    }
}
